<?php

namespace app\api\controller;

use app\common\controller\Api;

use think\Db;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use think\exception\PDOException;
use think\Exception;

/**
 * 示例接口
 */
class Excel extends Api
{

    //如果$noNeedLogin为空表示所有接口都需要登录才能请求
    //如果$noNeedRight为空表示所有接口都需要验证权限才能请求
    //如果接口已经设置无需登录,那也就无需鉴权了
    //
    // 无需登录的接口,*表示全部
    protected $noNeedLogin = ['*'];
    // 无需鉴权的接口,*表示全部
    protected $noNeedRight = ['test2'];

    private $orders_key = "vip_orders";
    private $orders_key2 = "vip_orders2";
    private $rollback = "vip_rollback";
    private $rollback2 = "vip_rollback2";
    private $timeout;
    public $caijinData;

    public function _initialize()
    {
        $this->caijinData = Db::name("vip_caijin")->order("save_money desc")->cache(true)->select();
        $this->timeout = (int)Db::name("config")->where("name","timeout")->value("value");
    }

    /**
     * 保存流水记录
     */
    public function save_orders()
    {
        ini_set("memory_limit","800M");
        set_time_limit(0);
        //$config = ["host"=>"127.0.0.1","port"=>6379];

        ////连接本地的 Redis 服务
        //$redis = new \Redis();
        //$redis->connect($config["host"], $config["port"]);

        //if($redis->lLen($this->orders_key) >= 1){
        //    echo "服务器繁忙";
        //    if(!$redis->get($this->orders_key2)){
        //        $redis->del($this->orders_key);
        //        echo "<br>过期删除list";
        //    }
        //    exit;
        //}
        //
        //$redis->set($this->orders_key2,"过期标识",180);

        //$redis = new RedisLock();
        //$key = "a";
        //$islock = $redis->lock($key,600);
        //dump($islock);
        //if (!$islock) {
        //    return json(["status"=>401,"msg"=>"系统繁忙"]);
        //}
        //exit;
        //sleep(10);

        //
        // 查询等待录入的Excel
        $excel = Db::name("vip_excels")->where("status",2)->order("weigh desc")->find();
        $savetime = empty($excel['addtime']) ? time() : strtotime($excel['addtime']);
        $this->edit_excels($excel["id"],3,"");

        //var_dump($excel);
        if(empty($excel)){
            $this->edit_excels($excel["id"],4,"无导入数据");
            return json(["status"=>200,"msg"=>"无导入数据"]);
        }
        $file = $excel["file"];

        if (!$file) {
            $this->edit_excels($excel["id"],4,__('Parameter %s can not be empty', 'file'));
            return json(["status"=>401,"msg"=>__('Parameter %s can not be empty', 'file')]);
        }
        $filePath = ROOT_PATH . DS . 'public' . DS . $file;
        if (!is_file($filePath)) {
            $this->edit_excels($excel["id"],4,__('No results were found'));
            return json(["status"=>401,"msg"=>__('No results were found')]);
        }
        //实例化reader
        $ext = pathinfo($filePath, PATHINFO_EXTENSION);
        if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {
            $this->edit_excels($excel["id"],4,__('Unknown data format'));
            return json(["status"=>401,"msg"=>__('Unknown data format')]);
        }
        if ($ext === 'csv') {
            $file = fopen($filePath, 'r');
            $filePath = tempnam(sys_get_temp_dir(), 'import_csv');
            $fp = fopen($filePath, "w");
            $n = 0;
            while ($line = fgets($file)) {
                $line = rtrim($line, "\n\r\0");
                $encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']);
                if ($encoding != 'utf-8') {
                    $line = mb_convert_encoding($line, 'utf-8', $encoding);
                }
                if ($n == 0 || preg_match('/^".*"$/', $line)) {
                    fwrite($fp, $line . "\n");
                } else {
                    fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n");
                }
                $n++;
            }
            fclose($file) || fclose($fp);

            $reader = new Csv();
        } elseif ($ext === 'xls') {
            $reader = new Xls();
        } else {
            $reader = new Xlsx();
        }

        //导入文件首行类型,默认是注释,如果需要使用字段名称请使用name
        $importHeadType = isset($this->importHeadType) ? $this->importHeadType : 'comment';

        $table = (new \app\admin\model\vip\Orders)->getQuery()->getTable();
        $database = \think\Config::get('database.database');
        $fieldArr = [];
        $list = db()->query("SELECT COLUMN_NAME,COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ? AND TABLE_SCHEMA = ?", [$table, $database]);
        foreach ($list as $k => $v) {
            if ($importHeadType == 'comment') {
                $fieldArr[$v['COLUMN_COMMENT']] = $v['COLUMN_NAME'];
            } else {
                $fieldArr[$v['COLUMN_NAME']] = $v['COLUMN_NAME'];
            }
        }

        //加载文件
        $insert = [];
        try {
            if (!$PHPExcel = $reader->load($filePath)) {
                $this->edit_excels($excel["id"],4,__('Unknown data format'));
                return json(["status"=>401,"msg"=>__('Unknown data format')]);
            }
            $currentSheet = $PHPExcel->getSheet(0);  //读取文件中的第一个工作表
            $allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
            $allRow = $currentSheet->getHighestRow(); //取得一共有多少行
            $maxColumnNumber = Coordinate::columnIndexFromString($allColumn);
            $fields = [];
            for ($currentRow = 1; $currentRow <= 1; $currentRow++) {
                for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                    $fields[] = $val;
                }
            }

            for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
                $values = [];
                for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                    $values[] = is_null($val) ? '' : $val;
                }
                $row = [];
                $temp = array_combine($fields, $values);
                foreach ($temp as $k => $v) {
                    if (isset($fieldArr[$k]) && $k !== '') {
                        $row[$fieldArr[$k]] = $v;
                    }
                }


                if ($row) {
                    // 处理每一个用户数据
                    $row['username'] = trim($row['username']);
                    $row["status"] = 0;
                    $row["savetime"] = $savetime;
                    $row["createtime"] = time();

                    $insert[] = $row;

                    // 分批导入
                    if(count($insert) >= 2000){
                        Db::name("vip_orders")->insertAll($insert);
                        unset($insert);
                        $insert = [];
                    }

                }
            }
            try{
                if(empty($insert)){
                    $this->edit_excels($excel["id"],1,"操作成功");
                    return json(["status"=>200,"msg"=>"操作成功"]);
                }
                Db::name("vip_orders")->insertAll($insert);
                $this->edit_excels($excel["id"],1,"操作成功");
                return json(["status"=>200,"msg"=>"操作成功"]);
            } catch (PDOException $exception) {
                $this->edit_excels($excel["id"],4,$exception->getMessage());
                return json(["status"=>401,"msg"=>$exception->getMessage()]);
            } catch (\Exception $e) {
                $this->edit_excels($excel["id"],4,$e->getMessage());
                return json(["status"=>401,"msg"=>$e->getMessage()]);
            }

        } catch (Exception $exception) {
            $this->edit_excels($excel["id"],4,$exception->getMessage());
            return json(["status"=>401,"msg"=>$exception->getMessage()]);
        }
    }

    /**
     * 修改Excel状态
     */
    public function edit_excels($id,$status,$msg)
    {
        //$redis->unlock($key);
        Db::name("vip_excels")->where("id",$id)->update(["status"=>$status,"createtime"=>time(),"msg"=>$msg]);
    }


    /**
     * 归档用户流水
     */
    public function save_user()
    {
        ini_set("memory_limit","800M");
        set_time_limit(0);
        $config = ["host"=>"127.0.0.1","port"=>6379];

        //连接本地的 Redis 服务
        $redis = new \Redis();
        $redis->connect($config["host"], $config["port"]);

        if($redis->lLen($this->orders_key) >= 1){
            echo "服务器繁忙";
            if(!$redis->get($this->orders_key2)){
                $redis->del($this->orders_key);
                echo "<br>过期删除list";
            }
            exit;
        }



        // 查询未导入的用户流水
        $ordersData = Db::name("vip_orders")->where("status",0)->limit(0,1000)->select();

        ///////
        if(!empty($ordersData)){
            $redis->lPush($this->orders_key,time());
            $redis->set($this->orders_key2,"过期标识",$this->timeout);
        }

        foreach($ordersData as $k=>$v) {
            $this->action_user($v);
            $this->haoyun($v);
        }
        $count = count($ordersData);
        if($count){
            return json(["status"=>200,"msg"=>"处理{$count}条数据完毕"]);
        } else {
            return json(["status"=>200,"msg"=>"暂无数据"]);
        }
    }

    /**
     * 保存用户数据表
     */
    public function action_user($row)
    {
        // 查询VIP等级配置信息
        $levelData = Db::name("vip_description")->order("save_money asc")->cache(true)->select();
        // 录入到用户表
        $userData = Db::name("vip_user")->where("username",$row["username"])->find();
        // 修改流水状态
        Db::name("vip_orders")->where("id",$row["id"])->update(["status"=>1]);
        if(!empty($userData)){
            $money = $userData["money"] + $row["money"];
            $level = $this->slevel($money,$levelData);
            // 查看是否晋级VIP
            $this->uplevel($level,0,$row["username"]);
            // 录入好运金
            Db::name("vip_user")->where("id",$userData["id"])->update(["money"=>$money,"level"=>$level,"updatetime"=>time()]);
        } else {
            $money = $row["money"];
            $level = $this->slevel($money,$levelData);
            Db::name("vip_user")->insert(["username"=>$row["username"],"money"=>$money,"money"=>$money,"level"=>$level,"createtime"=>time(),"updatetime"=>time()]);
            // 查看是否晋级VIP
            $this->uplevel($level,0,$row["username"]);
            //
        }
        // end
    }

    /**
     * 归档好运奖金
     * @param $row 用户名流水数据
     * @throws Exception
     */
    public function haoyun($row)
    {
        // 用户名 + 当前存入的金额 + 存入时间
        $username = $row["username"];
        $savemoney = $row["money"];

        $year = date('Y',$row["savetime"]);
        $month = date('m',$row["savetime"]) + 0;
        $day = date('d',$row["savetime"]);
        if($day >= 18) {
            $month = $month + 1;
        }
        $savetime = $year . "-" .$month;

        // 查询用户VIP等级
        $level = Db::name("vip_user")->where("username",$username)->value("level");
        // 查询本月用户是否存在
        $user = Db::name("vip_haoyun")->where("username",$username)->where("savetime",$savetime)->find();
        if(empty($user)){
            $money = $this->s_caijin($savemoney,$level);
            Db::name("vip_haoyun")->insert(["username"=>$username,"savetime"=>$savetime,"savemoney"=>$savemoney,"money"=>$money,"status"=>0]);
        } else {
            $savemoney = $user["savemoney"] + $savemoney;
            $money = $this->s_caijin($savemoney,$level);
            Db::name("vip_haoyun")->where("username",$username)->where("savetime",$savetime)->update(["savemoney"=>$savemoney,"money"=>$money]);
        }
    }

    /**
     * 查询当前彩金对应奖励
     */
    public function s_caijin($save_money,$level)
    {
        $caijinData = $this->caijinData;
        $money = 0;
        foreach($caijinData as $v){
            if($save_money >= $v["save_money"] && $level >= $v["level_start"]){
                $money = $v["money"];
                break;
            }
        }
        return $money;
    }

    /**
     * 测试好运金
     * /api/excel/s_caijin2?save_money=3000000.00&level=9
     */
    public function s_caijin2()
    {
        $save_money = input("save_money");
        $level = input("level");
        $caijinData = $this->caijinData;
        dump($caijinData);
        $money = 0;
        foreach($caijinData as $v){
            if($save_money >= $v["save_money"] && $level >= $v["level_start"]){
                $money = $v["money"];
                break;
            }
        }
        return $money;
    }

    /**
     * 判断是否晋级
     */
    public function uplevel($level,$now_level,$username)
    {
        if($level > $now_level){
            $level_money = Db::name("vip_description")->where("level","elt",$level)->cache(true)->select();//奖金
            foreach($level_money as $k=>$v){
                $jiangli[$k]["username"] = $username;
                $jiangli[$k]["money"] = $v["money"];
                $jiangli[$k]["level"] = $v["level"];
                $jiangli[$k]["status"] = 0;
                $jiangli[$k]["createtime"] = time();
            }
            if(!empty($jiangli)){
                Db::name("vip_jiangli")->insertAll($jiangli);
            }
        }
    }

    /**
     * 判断等级
     */
    public function slevel($num,$levelData)
    {
        $leve = 0;
        foreach($levelData as $v){
            if($num >= $v["save_money"]){
                $leve = $v["level"];
            }
        }
        return $leve;
    }

    /**
     * 回滚数据
     */
    public function rollback()
    {
        set_time_limit(0);
        ini_set("memory_limit","800M");
        //$config = ["host"=>"127.0.0.1","port"=>6379];
        //
        ////连接本地的 Redis 服务
        //$redis = new \Redis();
        //$redis->connect($config["host"], $config["port"]);
        //
        //if($redis->lLen($this->rollback) >= 1){
        //    echo "服务器繁忙";
        //    if(!$redis->get($this->rollback2)){
        //        $redis->del($this->rollback);
        //        echo "<br>过期删除list";
        //    }
        //    exit;
        //}



        // 查询VIP等级配置信息
        $levelData = Db::name("vip_description")->order("save_money asc")->cache(true)->select();
        // 回滚数据
        $data = Db::name("vip_orders")->where("status",2)->limit(0,1000)->select();
        if(empty($data)){
            return json(["status"=>401,"msg"=>"回滚数据为空"]);
        }

        ////////////
        //$redis->lPush($this->rollback,time());
        //$redis->set($this->rollback2,"过期标识",$this->timeout);

        foreach($data as $k=>$v)
        {
            // 减去用户总金额
            $userData = Db::name("vip_user")->where("username",$v['username'])->find();
            $money = $userData['money'] - $v['money'];
            Db::name("vip_user")->where("username",$v['username'])->setDec("money",$v['money']);
            // 查询是否降级
            $userLevel = $this->slevel($money,$levelData);
            // 删除用户流水记录
            Db::name("vip_orders")->where("id",$v['id'])->delete();
            Db::name("vip_user")->where("username",$v['username'])->update(["money"=>$v['money'],"level"=>$userLevel]);
            if($userLevel == $userData['level']){
                continue;
            }
            // 删除好运金记录
            $this->backHaoyun($v,$userLevel);
            // 删除晋级奖金
            for($i=$userData['level'];$i>=$userLevel;$i--) {
                Db::name("vip_jiangli")->where("username",$v['username'])->where("level",$i)->delete();
            }
        }
        return json(["status"=>200,"msg"=>"回滚成功"]);
    }

    /**
     * 撤回好运奖金
     * @param $row 用户名流水数据
     * @param $level 用户当前等级
     * @throws Exception
     */
    public function backHaoyun($row,$level)
    {
        // 用户名 + 当前存入的金额 + 存入时间
        $username = $row["username"];
        $savemoney = $row["money"];

        $year = date('Y',$row["savetime"]);
        $month = date('m',$row["savetime"]) + 0;
        $day = date('d',$row["savetime"]);
        if($day >= 18) {
            $month = $month + 1;
        }
        $savetime = $year . "-" .$month;

        // 查询本月用户是否存在
        $user = Db::name("vip_haoyun")->where("username",$username)->where("savetime",$savetime)->find();
        if(empty($user)){
            //
        } else {
            $savemoney = $user["savemoney"] - $savemoney;
            $money = $this->s_caijin($savemoney,$level);
            if($money == 0){
                Db::name("vip_haoyun")->where("username",$username)->where("savetime",$savetime)->delete();
            } else {
                Db::name("vip_haoyun")->where("username",$username)->where("savetime",$savetime)->update(["savemoney"=>$savemoney,"money"=>$money]);
            }
        }
    }

    public function clear()
    {
        $config = ["host"=>"127.0.0.1","port"=>6379];

        //连接本地的 Redis 服务
        $redis = new \Redis();
        $redis->connect($config["host"], $config["port"]);

        $redis->del($this->orders_key);
        $redis->del($this->orders_key2);
        //dump($this->caijinData);
        dump($this->timeout);
    }
}






















